package com.toycloud.system.utils;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.swing.*;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by xqyang5 on 2021/5/27.
 */
public class ExcelDealUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelDealUtil.class);

    private static HSSFWorkbook hWorkbook = null;
    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");


    /**
     * 写对象List到csv文件
     *
     * @param head
     * @param data
     * @return
     */
    public static String exportObjectToExcel(List<String> head, List<String> field, List<?> data,String savePath) throws IOException {
        //创建workbook
        hWorkbook = new HSSFWorkbook();
        //新建文件
        FileOutputStream fileOutputStream = null;
        HSSFRow headRow = null;
        HSSFRow contentRow = null;
        FileUtils.checkAndCreateDir(new File(savePath));
        String path = savePath + simpleDateFormat.format(new Date()) + ".xls";
        try {
            File fileDir = new File(path);
            FileUtils.checkAndCreateFile(fileDir);
//            CellStyle headStyle = hWorkbook.createCellStyle();
//            headStyle.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
//            headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//            headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//            HSSFFont font = hWorkbook.createFont();
//            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
//            headStyle.setFont(font);//选择需要用到的字体格式
//            CellStyle bodyStyle = hWorkbook.createCellStyle();
//            bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            String sheetNames = "sheet1";
            //添加sheet
            hWorkbook.createSheet(sheetNames);
            //添加表头, 创建第一行
            headRow = hWorkbook.getSheet(sheetNames).createRow(0);
            headRow.setHeight((short) (20 * 20));

            for (int j = 0; j < head.size(); j++) {
                HSSFCell cell = headRow.createCell(j);
                cell.setCellValue(head.get(j));
//                cell.setCellStyle(headStyle);
//                //加载下拉框
//                switch (j) {
//                    case 8:
//                        setHSSFValidation(hWorkbook.getSheet(sheetNames), AUDIO_VERSION_LIST, 0, data.size(), j, j);
//                        break;
//                    case 9:
//                        setHSSFValidation(hWorkbook.getSheet(sheetNames), WORD_VERSION_LIST, 0, data.size(), j, j);
//                        break;
//                    case 10:
//                        setHSSFValidation(hWorkbook.getSheet(sheetNames), AUDIO_SITUATION_LIST, 0, data.size(), j, j);
//                        break;
//                    case 11:
//                        setHSSFValidation(hWorkbook.getSheet(sheetNames), AUDIO_TYPE_LIST, 0, data.size(), j, j);
//                        break;
//                    case 12:
//                        setHSSFValidation(hWorkbook.getSheet(sheetNames), CATEGORY_LABEL_LIST, 0, data.size(), j, j);
//                        break;
//                    case 13:
//                        setHSSFValidation(hWorkbook.getSheet(sheetNames), ABILITY_LABEL_LIST, 0, data.size(), j, j);
//                        break;
//                    case 14:
//                        setHSSFValidation(hWorkbook.getSheet(sheetNames), THEME_LABEL_LIST, 0, data.size(), j, j);
//                        break;
//                    case 15:
//                        setHSSFValidation(hWorkbook.getSheet(sheetNames), KEYWORD_LIST, 0, data.size(), j, j);
//                        break;
//                    case 19:
//                        setHSSFValidation(hWorkbook.getSheet(sheetNames), SUITABLE_LABEL_LIST, 0, data.size(), j, j);
//                        break;
//                    default:
//                        break;
//                }
            }
            //写入数据，首先把对象转化为List
            List<List<String>> strData = objectTransformToList(field, data);
            for (int i = 0; i < strData.size(); i++) {
                List<String> list = strData.get(i);
                contentRow = hWorkbook.getSheet(sheetNames).createRow(i + 1);
                contentRow.setHeight((short) (20 * 20));
                for (int j = 0; j < list.size(); j++) {
                    HSSFCell cell = contentRow.createCell(j);
                    cell.setCellValue(list.get(j));
//                    cell.setCellStyle(bodyStyle);
                }
            }

            fileOutputStream = new FileOutputStream(fileDir);
            hWorkbook.write(fileOutputStream);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return path;
    }

    public static List<List<String>> objectTransformToList(List<String> fieldNameList, List<?> objects) {
        List<List<String>> result = new ArrayList<>();
        Object object = objects.get(0);
        Class c = object.getClass();
        //开始获得属性对应的数据
        objects.forEach(s -> {
            List<String> single = new ArrayList<>();
            for (int i = 0; i < fieldNameList.size(); i++) {
                Field field = null;
                try {
                    field = c.getDeclaredField(fieldNameList.get(i));
                    field.setAccessible(true);
                    Object o = field.get(s);
                    single.add(o == null ? "" : o.toString());
                } catch (Exception e) {
                    single.add("");
                    e.printStackTrace();
                }
            }
            result.add(single);
        });
        return result;
    }



    /**
     * 读取Sheet并转化为绘本对象列表
     *
     * @param sheet
     * @return
     * @tips Excel当某行设置了格式的时候，就会自动加入到getPhysicalNumberOfRows中，但实际上是空行，需要处理掉
     */
    public static <T> List<T> readSheetConvertList(Sheet sheet, List<String> fields, Class<T> c) {
        List<T> result = new ArrayList<>();
        Integer length = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < length; i++) {
            try {
                Row row = sheet.getRow(i);
                if (emptyRowCheck(row, fields.size())) {
                    continue;
                }
                T object = c.newInstance();
                for (int j = 0; j < fields.size(); j++) {
                    Cell cell = row.getCell(j);
                    String cellValue = "";
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING:
                                cellValue = cell.getStringCellValue().trim();
                                break;
                            case NUMERIC:
                                // 检查是否为日期类型
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    // 格式化日期为你想要的格式（比如 "yyyy/MM/dd"）
                                    cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date);
                                } else {
                                    // 处理数字类型
                                    cellValue = String.valueOf((int) cell.getNumericCellValue());
                                }
                                break;
                            // 处理其他类型如布尔类型等
                            default:
                                cellValue = "";
                                break;
                        }

//                        cellValue = cell.getStringCellValue().trim();
                    }
                    Field field = c.getDeclaredField(fields.get(j));
                    field.setAccessible(true);
                    field.set(object, cellValue);
                }
                result.add(object);
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("第" + i + "行出现错误");
            }

        }
        return result;
    }

    /**
     * 读取Excel
     *
     * @param filePath
     * @return
     */
    public static Workbook readExcel(File filePath) {
        String fileName = filePath.getName();
        String extString = fileName.substring(fileName.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取Excel表头
     *
     * @param firstRow
     * @return
     */
    public static List<String> readExcelHeader(Row firstRow) {
        List<String> headList = new ArrayList<>();
        int colNum = firstRow.getPhysicalNumberOfCells();
        for (int i = 0; i < colNum; i++) {
            Cell cell = firstRow.getCell(i);
            headList.add(cell.getStringCellValue());
        }
        return headList;
    }

    public static boolean emptyRowCheck(Row row, Integer columnNum) {
        boolean result = false;
        if (row == null) {
            result = true;
        } else {
            int emptyNum = 0;
            for (int i = 0; i < columnNum; i++) {
                Cell cell = row.getCell(i);
                if (cell == null) {
//                if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                    emptyNum++;
                }
            }
            result = emptyNum == columnNum;
        }
        return result;
    }

    /**
     * 写入
     * @param name Excel保存的主题名
     * @param data 里边有Map和List Map存放字段对应关系(ziDuan,字段的第一个字符是序号)
     *             List存放对象数据(listData)
     * @return [0]是fileName [1]是filePath
     */
    public static String[] objListToExcel(String name, Map data, String path) {
        Map<String, String> ziDuan = (Map<String, String>) data.get("ziDuan");
        List listData = (List) data.get("listData");
        Object[] keys = ziDuan.keySet().toArray();
        String[] ziDuanKeys = new String[keys.length];
        for (int k = 0; k < keys.length; k++) {
            String temp = keys[k].toString();
            int xuHao = Integer.valueOf(temp.replaceAll("[^0-9]",""));
            ziDuanKeys[xuHao] = temp.replaceAll("[\\d]","");
        }
        try {
            File newFile = new File(path);
            if (newFile.exists()) {
                int ii = JOptionPane.showConfirmDialog(null, "文件已存在，你确定要覆盖吗？", "文件已存在", JOptionPane.YES_NO_OPTION);
                if (ii == 0) {// 确定
                    newFile.createNewFile();
                    System.out.println("创建文件成功：" + path);
                    HSSFWorkbook wb = new HSSFWorkbook();
                    HSSFSheet sheet = wb.createSheet();

                    for (int i = -1; i < listData.size(); i++) {
                        HSSFRow row = sheet.createRow(i+1);
                        Object obj = new Object();
                        if(i!=-1) {
                            obj = listData.get(i);
                        }
                        for (int j = 0; j < ziDuanKeys.length; j++) {
                            HSSFCell cell = row.createCell(j);
                            if (i == -1) {
                                sheet.setColumnWidth(j, 6000);
                                cell.setCellValue(new HSSFRichTextString(ziDuan.get(j + ziDuanKeys[j])));
                            } else {
                                String ziDuanName = (String) ziDuanKeys[j];
//                                System.out.println(ziDuanName);
                                ziDuanName = ziDuanName.replaceFirst(ziDuanName.substring(0, 1),
                                        ziDuanName.substring(0, 1).toUpperCase());
                                ziDuanName = "get" + ziDuanName;
                                Class clazz = Class.forName(obj.getClass().getName());
                                Method[] methods = clazz.getMethods();
                                Pattern pattern = Pattern.compile(ziDuanName);
                                Matcher mat = null;
                                for (Method m : methods) {
                                    mat = pattern.matcher(m.getName());
                                    if (mat.find()) {
                                        Object shuXing = m.invoke(obj, null);
                                        if (shuXing != null) {
                                            cell.setCellValue(shuXing.toString());// 这里可以做数据格式处理
                                        } else {
                                            cell.setCellValue("");
                                        }
                                        break;
                                    }
                                }
                            }
                        }
                    }
                    OutputStream out = new FileOutputStream(path);
                    wb.write(out);// 写入File
                    out.flush();
                    out.close();
                }
            } else {
                newFile.createNewFile();
                System.out.println("创建文件成功：" + path);
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet();

                for (int i = -1; i < listData.size(); i++) {
//                    System.out.println(listData.size());
                    HSSFRow row = sheet.createRow(i+1);
                    Object obj = new Object();
                    if(i!=-1) {
                        obj = listData.get(i);
                    }
                    for (int j = 0; j < ziDuanKeys.length; j++) {
                        HSSFCell cell = row.createCell(j);
                        if (i == -1) {
                            sheet.setColumnWidth(j, 6000);
                            cell.setCellValue(new HSSFRichTextString(ziDuan.get(j + ziDuanKeys[j])));
                        } else {
                            String ziDuanName = (String) ziDuanKeys[j];
//                            System.out.println(ziDuanName);
                            ziDuanName = ziDuanName.replaceFirst(ziDuanName.substring(0,1),
                                    ziDuanName.substring(0,1).toUpperCase());
                            ziDuanName = "get" + ziDuanName;
                            Class clazz = Class.forName(obj.getClass().getName());
                            Method[] methods = clazz.getMethods();
                            Pattern pattern = Pattern.compile(ziDuanName);
                            Matcher mat = null;
                            for (Method m : methods) {
                                mat = pattern.matcher(m.getName());
                                if (mat.find()) {
                                    Object shuXing = m.invoke(obj, null);
                                    if (shuXing != null) {
                                        cell.setCellValue(shuXing.toString());// 这里可以做数据格式处理
                                    } else {
                                        cell.setCellValue("");
                                    }
                                    break;
                                }
                            }
                        }
                    }
                }
                OutputStream out = new FileOutputStream(path);
                wb.write(out);// 写入File
                out.flush();
                out.close();
            }

            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

}
